<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Account_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
    
    var $table = null;
    
    public function dodol()
    { return 'hello'; }
    
    function get_account($cla=null)
    {
        $this->db->select('accounts.id, accounts.name, accounts.code');
        $this->db->from('gls, transactions, accounts');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('accounts.classification_id', $cla);
        $this->db->order_by('accounts.code','asc');
        $this->db->distinct();
        return $this->db->get(); 
    }
    
    function get_all_account($cla=null)
    {
        $this->db->select('accounts.id, accounts.name, accounts.code, classification_id');
        $this->db->from('accounts');
        $this->cek_null($cla, 'classification_id');
        $this->db->order_by('accounts.code','asc');
        $this->db->distinct();
        return $this->db->get(); 
    }
    
    function get_balance($acc=null,$month=null,$year=null)
    {
//        $this->db->select('accounts.id, gls.id, gls.no, gls.dates, gls.currency, gls.notes, gls.balance,
//                           transactions.debit, transactions.credit, transactions.vamount, gls.approved');
        
        $this->db->select_sum('transactions.vamount');
        $this->db->select_sum('transactions.debit');
        $this->db->select_sum('transactions.credit');
        
        $this->db->from('gls, transactions, accounts');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('MONTH(dates)', $month);
        $this->db->where('YEAR(dates)', $year);
        $this->cek_null($acc,"transactions.account_id");
        $this->db->where('gls.approved', 1);
        return $this->db->get(); 
    }
    
    function get_period_balance($cur='IDR',$acc=null,$month=null,$year=null,$emonth=null,$eyear=null)
    {
//        $this->db->select('accounts.id, gls.id, gls.no, gls.dates, gls.currency, gls.notes, gls.balance,
//                           transactions.debit, transactions.credit, transactions.vamount, gls.approved');
        
        $this->db->select_sum('transactions.vamount');
        $this->db->select_sum('transactions.debit');
        $this->db->select_sum('transactions.credit');
        
        $this->db->from('gls, transactions, accounts');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->cek_between_month($month, $emonth);
        $this->cek_between_year($year, $eyear);
        $this->db->where('gls.currency', $cur);
//        $this->db->where('MONTH(dates)', $month);
//        $this->db->where('YEAR(dates)', $year);
        $this->cek_null($acc,"transactions.account_id");
        $this->db->where('gls.approved', 1);
        return $this->db->get(); 
    }
    
    function get_balance_by_classification($cur='IDR',$cla=null,$month=null,$year=null,$emonth=null,$eyear=null)
    {
//        $this->db->select('accounts.id, gls.id, gls.no, gls.dates, gls.currency, gls.notes, gls.balance,
//                           transactions.debit, transactions.credit, transactions.vamount, gls.approved');
        
        $this->db->select_sum('transactions.vamount');
        $this->db->select_sum('transactions.debit');
        $this->db->select_sum('transactions.credit');
        
        $this->db->from('gls, transactions, accounts, classifications');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('accounts.classification_id = classifications.id');
//        $this->db->where('MONTH(gls.dates)', $month);
//        $this->db->where('YEAR(gls.dates)', $year);
        $this->cek_between_month($month, $emonth);
        $this->cek_between_year($year, $eyear);
        $this->db->where('gls.currency', $cur);
        $this->cek_null($cla,"classifications.id");
        $this->db->where('gls.approved', 1);
        $res = $this->db->get()->row(); 
        return intval($res->vamount);
    }
    
    function get_start_balance_by_classification($cur='IDR',$cla=null,$start=null)
    {
//        $this->db->select('accounts.id, gls.id, gls.no, gls.dates, gls.currency, gls.notes, gls.balance,
//                           transactions.debit, transactions.credit, transactions.vamount, gls.approved');
        
        $this->db->select_sum('transactions.vamount');
        $this->db->select_sum('transactions.debit');
        $this->db->select_sum('transactions.credit');
        
        $this->db->from('gls, transactions, accounts, classifications');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('accounts.classification_id = classifications.id');
        $this->cek_between($start, $start);
        $this->db->where('gls.currency', $cur);
        $this->cek_null($cla,"classifications.id");
        $this->db->where('gls.approved', 1);
        $res = $this->db->get()->row(); 
        return intval($res->vamount);
    }
    
    function get_begining_balance_classification($cur='IDR',$cla=null,$start=null)
    {   
        $this->db->select_sum('balances.beginning');
        
        $this->db->from('balances, accounts, classifications');
        $this->db->where('balances.account_id = accounts.id');
        $this->db->where('accounts.classification_id = classifications.id');
        $this->db->where('balances.currency', $cur);
        $this->cek_null($cla,"classifications.id");
        $this->db->where('balances.month', date('n',  strtotime($start)));
        $this->db->where('balances.year', date('Y',  strtotime($start)));
        $res = $this->db->get()->row(); 
        return intval($res->beginning);
    }
    
    function get_cash_flow_acc($cur='IDR',$cla=null,$start=null,$end=null)
    {
        $this->db->select('accounts.id, accounts.name, accounts.code, accounts.classification_id');
        $this->db->from('gls, transactions, accounts, classifications');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('accounts.classification_id = classifications.id');

        $this->cek_between($start, $end);
        $this->db->where('gls.currency', $cur);
        $this->db->where('accounts.classification_id',$cla);
        $this->db->where('gls.approved', 1);
        $this->db->where('gls.cf', 1);
        $this->db->distinct();
        return $this->db->get(); 
    }
    
    function get_cash_flow($cur='IDR',$acc=null,$start=null,$end=null)
    {   
        $this->db->select_sum('transactions.vamount');
        $this->db->select_sum('transactions.debit');
        $this->db->select_sum('transactions.credit');
        
        $this->db->from('gls, transactions, accounts, classifications');
        $this->db->where('gls.id = transactions.gl_id');
        $this->db->where('transactions.account_id = accounts.id');
        $this->db->where('accounts.classification_id = classifications.id');

        $this->cek_between($start, $end);
        $this->db->where('gls.currency', $cur);
        $this->db->where('transactions.account_id',$acc);
        $this->db->where('gls.approved', 1);
        $this->db->where('gls.cf', 1);
        $res = $this->db->get()->row(); 
        return intval($res->vamount);
    }
    
    private function cek_between_month($start,$end)
    {
        if ($start == null || $end == null ){return null;}
        else { return $this->db->where("MONTH(gls.dates) BETWEEN '".$start."' AND '".$end."'"); }
    }
    
    private function cek_between_year($start,$end)
    {
        if ($start == null || $end == null ){return null;}
        else { return $this->db->where("YEAR(gls.dates) BETWEEN '".$start."' AND '".$end."'"); }
    }
    
    private function cek_between($start,$end)
    {
        if ($start == null || $end == null ){return null;}
        else { return $this->db->where("gls.dates BETWEEN '".$start."' AND '".$end."'"); }
    }

    private function cek_null($val,$field)
    {
        if ($val == ""){return null;}
        else {return $this->db->where($field, $val);}
    }

}

?>